package com.apestech.api.condition;

import com.apestech.api.xml.esb.EsbUtil;
import com.apestech.framework.db.SqlSession;
import com.apestech.framework.dictionary.modul.ImportedKey;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.sql.SqlUtil;
import com.apestech.framework.sql.modul.FromList;
import com.apestech.framework.util.LocalVariable;
import com.apestech.framework.util.StringUtil;
import com.apestech.framework.util.callback.SpiltStrCallback;
import com.apestech.framework.xml.plugin.modul.SysCondition;

import java.util.ArrayList;

public class ConFieldM extends StringUtil {

    public class TableData{  //相关表元数据
        private String field;    //界面字段名SP_ID_FL_ID_NAME
        private String rfield;
        private String talbeName;
        private String type;
        private String remarks;
        private ImportedKey.ImportedField imp;
        private JSONObject childData=new JSONObject();      //相关配置字段
        private Table.Field dfield;
        private JSONObject fieldD;
        private JSONObject mdef;

        public String getField() {
            return field;
        }

        public void setField(String field) {
            this.field = field;
        }

        public String getRfield() {
            return rfield;
        }

        public void setRfield(String rfield) {
            this.rfield = rfield;
        }

        public String getTalbeName() {
            return talbeName;
        }

        public void setTalbeName(String talbeName) {
            this.talbeName = talbeName;
        }

        public String getType() {
            return type;
        }

        public void setType(String type) {
            this.type = type;
        }

        public String getRemarks() {
            return remarks;
        }

        public void setRemarks(String remarks) {
            this.remarks = remarks;
        }

        public ImportedKey.ImportedField getImp() {
            return imp;
        }

        public void setImp(ImportedKey.ImportedField imp) {
            this.imp = imp;
        }

        public TableData getChildData(String cfield) {
            return (TableData) childData.get(cfield);
        }

        public void setChildData(TableData ctabdata) {
            childData.put(ctabdata.getField(), ctabdata);
        }

        public ArrayList<String> getChildKey(){
            return childData.getKeylist();
        }

        public Table.Field getDfield() {
            return dfield;
        }

        public void setDfield(Table.Field dfield) {
            this.dfield = dfield;
        }

        public JSONObject getFieldD() {
            return fieldD;
        }

        public void setFieldD(JSONObject fieldD) {
            this.fieldD = fieldD;
        }

        public JSONObject getMdef() {
            return mdef;
        }

        public void setMdef(JSONObject mdef) {
            this.mdef = mdef;
        }
    }

    public class SqlData{
        private String osql;  //原始SQL，数据来源为元数据的SQL信息
        private String select;
        private String from_o;  //老的FROM部分内容
        private String from;  //新的FROM部分内容，别名差别
        private String where_o;  //老的WHERE部分内容
        private String where;  //新的WHERE部分内容，去掉与策略表关联，新的别名
        private FromList fromlist;  //SQL里用到的相关表里的档案记录

        public String getOsql() {
            return osql;
        }

        public void setOsql(String osql) {
            this.osql = osql;
        }

        public String getFrom_o() {
            return from_o;
        }

        public void setFrom_o(String from_o) {
            this.from_o = from_o;
        }

        public String getFrom() {
            return from;
        }

        public void setFrom(String from) {
            this.from = from;
        }

        public String getWhere_o() {
            return where_o;
        }

        public void setWhere_o(String where_o) {
            this.where_o = where_o;
        }

        public String getWhere() {
            if(where==null) {
                where="";
            }
            return where;
        }

        public void setWhere(String where) {
            this.where = where;
        }

        public FromList getFromlist() {
            return fromlist;
        }

        public void setFromlist(FromList fromlist) {
            this.fromlist = fromlist;
        }

        public String getSelect() {
            return select;
        }

        public void setSelect(String select) {
            this.select = select;
        }
    }

    private String field;  //制单界面字段ID
    private String cltableName;  //策略表名
    private Table clTable;   //策略表
    private TableData tableData;
    private SqlData sqlData;
    private SqlSession session;
    private SysCondition sysCondition;
    private String sql;

    private String role="";  //权限字串
    private String alais="A";
    private JSONObject fieldD; //界面配置
    private JSONObject mdef;  //维度数据
    private ImportedKey.ImportedField mimp;
    private Table.Field dfield;  //字段信息
    private JSONObject formJSON;

    public ConFieldM(String field, SqlSession session, JSONObject data) throws Exception {
        this.field=field;
        this.session=session;
        this.formJSON=new JSONObject();
        cltableName = data.getString("tablename");
        if(cltableName==null){
            cltableName = data.getString("foreignTab");
        }
        if(cltableName==null){
            cltableName = data.getString("table");
        }
        clTable=session.getDictTable(cltableName);
        String rid=data.getString("field");
        String remark=data.getString("comment");
        if(remark==null){
            remark="";
        }
        if(remark.indexOf(",")>=0){
            remark=remark.split(",")[0];
        }
        remark=remark.replace("编码","");
        remark=remark.replace("编号","");
        String purpose="pp_operate";
        tableData=new TableData();
        tableData.setField(field);
        if(data!=null){
            tableData.setField(field);
            tableData.setRfield(field);
            fieldD=new JSONObject(data);
            tableData.setFieldD(data);
            tableData.setFieldD(data);
            if(clTable!=null){
                dfield=clTable.getField(rid);
            }
            tableData.setDfield(dfield);
            if(fieldD.get("label")!=null){
                tableData.setRemarks(fieldD.getString("label"));
            } else {
                tableData.setRemarks(remark);
            }
            if(data.size()>2){
                mdef=new JSONObject(data);
                tableData.setMdef(mdef);
            }
        }
    }

    public ConFieldM(String field, Object clTable, SqlSession session, String purpose, String alais, JSONObject formJSON) throws Exception {
        this.session=session;
        if(clTable instanceof Table){
            this.clTable=(Table)clTable;
        } else if(clTable instanceof String){
            this.clTable=session.getDictTable((String) clTable);
        }
        cltableName=this.clTable.getTabname();
        this.formJSON=formJSON;
        if(alais!=null){
            this.alais=alais;
        }
        fieldD=getDField(field);
        this.field=field;
        this.cltableName=cltableName;
        sysCondition= LocalVariable.getConfig().getSysCondition(session);
        mdef= sysCondition.getDimensionField(session.getTheme()+"."+field);
        tableData=new TableData();
        tableData.setField(field);
        tableData.setTalbeName(cltableName);
        tableData.setMdef(mdef);
        tableData.setImp(mimp);
        dfield=this.clTable.getField(field);
        tableData.setDfield(dfield);
        tableData.setType(this.clTable.getDbType(field));
        tableData.setRemarks(this.clTable.getOremarks(field));
        tableData.setRfield(field);
        if(dfield==null){
            throw new Exception("字段["+field+"]必须为策略表里的实际字段！");
        }
        sqlData=new SqlData();
        boolean flag=this.clTable.isFkField(field);
        if(!flag && "tempSql".equals(cltableName)){
            flag=(mdef!=null);
        }
        if(flag){
            if(mdef!=null){
                this.sql=mdef.getString("sql");
                if(!"tempSql".equals(cltableName)){
                    mimp=this.clTable.getPkFKey().get(field);
                }
            }

            if(!"1".equals(fieldD.get("no_role"))){
                if(mdef!=null){
                    String copyField=mdef.getString("copyField");
                    if(purpose!=null){
                        JSONObject rm=sysCondition.getRolefield(field);
                        if(rm==null && copyField!=null){
                            rm=sysCondition.getRolefield(copyField);
                        }
                        if(rm!=null){
                            //pp_belong pp_search pp_operate
                            int pos=field.indexOf(".");
                            String f;
                            if(pos<0){
                                f=field;
                            } else {
                                f=field.substring(pos+1, field.length());
                            }
                            role="<auth field=\""+f+"\" type=\""+rm.getString("type")+"\" purpose=\""+purpose+"\" exp=\""+f+"\"  />";
                        }
                    }
                }
            }
            addTable();
            addSql();
        } else {
            sqlData.setSelect(alais+"."+field);
            sqlData.setWhere("");
        }

    }

    private JSONObject getDField(String fid) throws Exception {
        JSONObject f= new JSONObject(EsbUtil.getfieldD(fid));  //增加这部分逻辑，将同一个外键字段相关特殊情况集中处理
        if(formJSON!=null){
            f.putAll((JSONObject)formJSON.get(fid));
        }
        return f;
    }

    private void addTable() throws Exception {
        if(mdef!=null){  //外键
            String auxiliaryField=mdef.getString("auxiliaryField");
            String fkt_name=mdef.getString("foreignTab");
            if(fkt_name==null){
                fkt_name=mdef.getString("table");
            }
            Table fktab=session.getDictTable(fkt_name);
            final String[] t = new String[1];
            execSplitStr(auxiliaryField, ",", new SpiltStrCallback() {
                @Override
                public void exec(String id) throws Exception {
                    if(t[0]==null){
                        setCTableData(field+"_"+id, fktab);
                        t[0] =id;
                    }
                }
            });

            String conqry=fieldD.getString("conqry");
            if(conqry==null){
                conqry="";
            } else {
                conqry=conqry.replace(" ","");
            }
            String display=fieldD.getString("display");
            if(display==null){
                display="";
            } else {
                display=display.replace(" ","");
            }
            conqry=conqry+","+display;
            if(conqry!=null){
                execSplitStr(conqry, ",", new SpiltStrCallback() {
                    @Override
                    public void exec(String id) throws Exception {
                        if(!"".equals(id.trim())){
                            setCTableData(field+"_"+id, fktab);
                        }
                    }
                });
            }
        }
    }

    private void setCTableData(String id, Table fktab) throws Exception {
        if("tempSql".equals(cltableName) || fktab==null){
            return;
        }
        TableData zf=new TableData();
        if(fktab.getTabname().equals("MAS_PARTY_ROLE")) {
            zf.setField((id));
            zf.setTalbeName("MAS_PARTY");
            Table tab = session.getDictTable(zf.getTalbeName());
            zf.setRfield("NAME");
            zf.setImp(fktab.getPkFKey().get("PARTY_ID"));
            zf.setType(tab.getDbType("NAME"));
            zf.setRemarks(tab.getRemarks("NAME"));
            zf.setDfield(tab.getField("NAME"));
        } else if(fktab.getTabname().equals("MAS_ENUMERATION")){
            zf.setField((field+"_DESCRIPTION"));
            zf.setRfield("DESCRIPTION");
            zf.setTalbeName(fktab.getTabname());
            zf.setType(fktab.getDbType("DESCRIPTION"));
            zf.setRemarks(fktab.getRemarks("DESCRIPTION"));
            zf.setDfield(fktab.getField("DESCRIPTION"));
        } else {
            ArrayList<String> klist=fktab.getField().getKeylist();
            for(String k : klist){
                int pos1=(id+"_").indexOf(field+"_"+k+"_");
                if(pos1>=0){
                    zf.setRfield(k);
                    int pos2=(field+"_"+k).length();
                    int len=id.length();
                    if(pos2==len){  //外键表自身字段
                        zf.setField(id);
                        zf.setFieldD(getDField(id));
                        zf.setTalbeName(fktab.getTabname());
                        if(zf.getFieldD().get("label")!=null){
                            zf.setRemarks(zf.getFieldD().getString("label"));
                        } else {
                            zf.setRemarks(fktab.getRemarks(k));
                        }
                        zf.setType(fktab.getDbType(k));
                        zf.setDfield(fktab.getField(zf.getRfield()));
                        if(fktab.isFkField(k)){
                            zf.setMdef(sysCondition.getDimensionField(session.getTheme()+"."+k));
                        }
                        break;
                    } else {  //外键表对应外键下的关联字段
                        ImportedKey imp1=fktab.getPkFKey();
                        String f2=id.substring(pos1+pos2+1, len);  //flid
                        zf.setImp(imp1.get(k));
                        zf.setField(id);
                        zf.setFieldD(getDField(id));
                        String tname=imp1.get(k).getPkTableName();
                        Table tab2=session.getDictTable(tname);
                        zf.setTalbeName(tname);
                        if(zf.getFieldD().get("label")!=null){
                            zf.setRemarks(zf.getFieldD().getString("label"));
                        } else {
                            zf.setRemarks(tab2.getRemarks(f2));
                        }
                        zf.setType(tab2.getDbType(f2));
                        //zf.setRfield(zf.getImp().getPkColumnName());
                        zf.setRfield(id.substring(pos2+1,len));
                        zf.setDfield(tab2.getField(zf.getRfield()));
                        break;
                    }
                }
            }
        }
        tableData.setChildData(zf);
    }

    private void addSql() throws Exception {
        String conqry=fieldD.getString("conqry");
        if(conqry==null){
            conqry="";
        } else {
            conqry=conqry.replace(" ","");
        }
        String display=fieldD.getString("display");
        if(display==null){
            display="";
        } else {
            display=display.replace(" ","");
        }
        conqry=conqry+","+display;
        if(mimp==null){
            return;
        }
        int pos=sql.indexOf("<if");
        String osql=sql;
        if(pos>0){
            osql=sql.substring(0, pos);
        }
        int posf=osql.lastIndexOf("FROM");
        int posw=osql.indexOf("WHERE", posf);
        sqlData.setFrom_o(osql.substring(posf+4, posw));
        sqlData.setWhere_o(osql.substring(posw, osql.length()));
        sqlData.setFromlist(new FromList(sqlData.getFrom_o(), null, session));
        sqlData.setOsql(osql);

        ArrayList<String> klist=tableData.getChildKey();
        String select="";
        String from_n=sqlData.getFrom_o();
        String where_n=sqlData.getWhere_o();
        int pv=where_n.indexOf("VALID");
        int len=where_n.length();
        int pvs=-1;
        int pve=-1;
        if(pv>=0){
            for(int i=pv; i>=0; i--){
                String s=where_n.substring(i, i+1);
                if(s.equals(" ")){
                    pvs=i;
                    break;
                }
            }
            for(int i=pv; i<len; i++){
                String s=where_n.substring(i, i+1);
                if(s.equals(" ")){
                    pve=i;
                    break;
                }
            }
            String valid=where_n.substring(pvs, pve);
            where_n=where_n.replace(valid, " 1=1 ");
        }

        int i=0;
        for(SqlUtil.From f: sqlData.getFromlist().getTablist()){
            String a=f.getAlias();
            from_n=from_n.replace(" "+a, " S"+i);
            where_n=where_n.replace(a+".", "S"+i+".");
            i++;
        }
        sqlData.setFrom(from_n);
        sqlData.setWhere(where_n);
        where_n="";

        for (String k:klist) {
            TableData tab=tableData.getChildData(k);
            String tk=rightStr(k, k.length()-field.length()-1);
            boolean iswhere=(","+conqry+",").indexOf(","+tk+",")>=0;
            boolean flag = iswhere;
            String where="";
            if(!"".equals(select)){
                select=select+",";
            }
            if(tab.getImp()==null){
                select=select+" ( SELECT S0."+tab.getRfield()+" FROM "+tab.getTalbeName()+" S0 "+
                        " WHERE 1=1 AND S0."+mimp.getPkColumnName()+"="+alais+"."+field+") "+tab.getField();
            } else {
                select=select+" ( SELECT S0."+tab.getRfield()+" FROM "+tab.getTalbeName()+" S0,"+mimp.getPkTableName()+" S1"+
                        " WHERE 1=1 AND S0."+tab.getImp().getPkColumnName()+"=S1."+tab.getImp().getColumnName()+
                        "   AND S1."+mimp.getPkColumnName()+"="+alais+"."+field+") "+tab.getField();
                if(iswhere){
                    if (!sqlData.getFromlist().hasTable(tab.getTalbeName())) {  //所关联的表是维度SQL中FROM的一部分，需要调整对应别名顺序
                        where = " AND EXISTS (SELECT 1 FROM " + tab.getTalbeName() +
                                " Y WHERE Y." + tab.getImp().getPkColumnName() +
                                "=S" + sqlData.getFromlist().getTableIndex(tab.getImp().getPkTableName()) + "." + tab.getImp().getColumnName() +
                                " AND Y." + tab.getImp().getPkColumnName() + "=" + alais + "." + tab.getImp().getColumnName();
                        Table ctab=session.getDictTable(tab.getTalbeName());

                        if(ctab.getField(tab.getRfield()).getTreeId()==null){
                            where = where + "<foreach item=\"item\" index=\"index\" collection=\""+k+"\"  open=\" AND Y." + tab.getRfield() +"IN (\" separator=\" , \" close=\")\">";
                            where = where + "#{item." + k + "}";
                            //where = where + " Y." + tab.getRfield() + " LIKE '%'||#{item." + k + "}||'%' ) ";
                        } else {
                            where = where + "<foreach item=\"item\" index=\"index\" collection=\""+k+"\"  open=\" AND (\" separator=\" or \" close=\")\">";
                            where = where + " Y." + tab.getRfield() + " LIKE #{item." + k + "}||'%' ) ";
                        }
                        where = where + "</foreach>";

                        flag = false;
                    }
                } else {
                    flag=false;
                }
            }
            if (flag) {
                where = where + " AND "+alais+"."+field+"=S"+sqlData.getFromlist().getTableIndex(mimp.getPkTableName()) + "." +mimp.getPkColumnName();
                String were=" S" + sqlData.getFromlist().getTableIndex(tab.getTalbeName()) + "." ;
                Table ctab=session.getDictTable(tab.getTalbeName());

                if(ctab.getField(tab.getRfield()).getTreeId()==null){
                    where = where + "<foreach item=\"item\" index=\"index\" collection=\""+k+"\"  open=\" AND "+were + tab.getRfield()+" IN (\" separator=\" , \" close=\")\">";
                    where = where + "#{item." + k + "}";
                } else {
                    where = where + "<foreach item=\"item\" index=\"index\" collection=\""+k+"\"  open=\" AND (\" separator=\" or \" close=\")\">";
                    where = where + were+ tab.getRfield() + " LIKE #{item." + k + "}||'%' ";
                }
                where = where + "</foreach>";
            }
            if(!"".equals(where)){
                where= "<if test=\""+k+"!=null\"> AND EXISTS (SELECT 1 FROM "+sqlData.getFrom()+" "+sqlData.getWhere()+" "+where+") </if>";
                where_n=where_n+where;
            }
        }
        sqlData.setWhere(where_n);
        if("".equals(select)){
            sqlData.setSelect(alais+"."+field);
        } else {
            sqlData.setSelect(alais+"."+field+","+select);
        }

    }

    public SqlData getSqlData() {
        return sqlData;
    }

    public String getRole() {
        return role;
    }

    public Table.Field getDfield(String fid) {
        if(fid.equals(field)){
            return dfield;
        } else {
            return tableData.getChildData(fid).getDfield();
        }
    }

    public JSONObject getMdef(String fid) {
        if(fid.equals(field)){
            return mdef;
        } else {
            return tableData.getChildData(fid).getMdef();
        }
    }

    public SysCondition getSysCondition() {
        return sysCondition;
    }

    public SqlSession getSession() {
        return session;
    }

    public TableData getTableData() {
        return tableData;
    }

    public TableData getTableData(String fid) {
        if(fid.equals(field)){
            return tableData;
        } else {
            return tableData.getChildData(fid);
        }
    }

    public boolean isForkey(String fid){
        boolean flag=false;
        JSONObject m=getMdef(fid);
        if(m!=null && (m.get("table")!=null || m.get("foreignTab")!=null)){
            flag=true;
        }
        return flag;
    }

    public ImportedKey.ImportedField getImp(String fid){
        if(fid.equals(field)){
            return mimp;
        } else {
            if(tableData.getChildData(fid).getImp()!=null){
                return tableData.getChildData(fid).getImp();
            } else {
                return mimp;
            }
        }

    }

    public String getField() {
        return field;
    }

    public JSONObject getFieldD(String fid) throws Exception {
        if(fid.equals(field)){
            return fieldD;
        } else {
            return tableData.getChildData(fid).getFieldD();
        }
    }

    public String getRfield(String fid) {
        if(fid.equals(field)){
            return tableData.getRfield();
        } else {
            return tableData.getChildData(fid).getRfield();
        }
    }

    public String getRemark(String fid){
        String remark=null;
        if(fid.equals(field)){
            remark=tableData.getRemarks();
            if(remark==null && mdef!=null){
                remark=mdef.getString("comment");
            }
        } else {
            remark=tableData.getChildData(fid).getRemarks();
        }
        if(remark==null){
            remark="";
        }
        return remark;
    }

}
